package cc.mrbird.febs.statistics.service.impl;

import cc.mrbird.febs.accounting.Constant.CompanyConstant;

import cc.mrbird.febs.accounting.dto.detailCost.DetailTotalDTO;
import cc.mrbird.febs.accounting.dto.standardSub.SubExportData;
import cc.mrbird.febs.accounting.dto.standardSub.SubMainDetailDTO;
import cc.mrbird.febs.accounting.entity.*;
import cc.mrbird.febs.accounting.mapper.DetailCostMapper;
import cc.mrbird.febs.accounting.service.*;
import cc.mrbird.febs.common.utils.FebsUtil;
import cc.mrbird.febs.statistics.service.IDataExportService;
import cc.mrbird.febs.utils.*;
import io.swagger.models.auth.In;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.stereotype.Service;


import javax.annotation.Resource;
import javax.script.ScriptEngine;
import javax.script.ScriptEngineManager;
import javax.script.ScriptException;
import java.math.BigDecimal;
import java.util.*;
import java.util.stream.Collectors;

import static cc.mrbird.febs.utils.CodeUtils.getRuleCodeSet;

/**
 *  Service实现
 *
 * @author baoyinlei
 * @date 2021-09-28 23:27:01
 */
@Service
public class DataExportServiceImpl implements IDataExportService {

    @Resource
    private IStandardSubService standardSubService;
    @Resource
    private DetailCostMapper detailCostMapper;


    @Override
    public void queryDetailForExcel(List<Project> projects,  List<ProjectType> types, String year, List<String> yearMonths, Integer excelType, XSSFWorkbook workbook) {
        Map<String, Double> codeValueMap = new HashMap<>();
        Map<String, Double> codeValueMap1 = new HashMap<>();   // 存储所需要的所有数据
        Map<String, Double> codeValueMap4 = new HashMap<>();    // 存储补贴合计
        Set<SubMainDetailDTO> subExportDataSet = new HashSet<>();  // 存储第二列数据的名字和code
        Set<String> codes = new HashSet<>();   // 存储第二列数据的code
        int subTYpeMax = excelType + 10;
        for (Project project : projects) {
            Map<String, Double> codeValueMap2 = new HashMap<>();   // 存储场地费
            Map<String, Double> codeValueMap3 = new HashMap<>();    // 存储日常费用
            Long projectId = project.getId();
            // 通过项目id和时间 获取dataExport表所有的数据信息
            // todo
//            List<DetailCost> dataExports = new ArrayList<>();
            List<DetailCost> dataExports = detailCostMapper.queryDetailCodeNoCompany(projectId, yearMonths);
            dataExports = DetailCostUtil.mergeDetailCost(dataExports);
            // 通过项目id和时间 获取所有的科目信息: 标准科目(bk_standard_sub) + 自定义科目（bk_standard_sub_extra）
//            List<SubMainDetailDTO> subMainDetailDTOS = new ArrayList<>();
            // todo
            List<SubMainDetailDTO> subMainDetailDTOS = this.standardSubService.queryAllSubDetailNoCompany(projectId,yearMonths);
            subMainDetailDTOS.addAll(SubjectUtil.otherSubjects);
            // 将所有科目放入map中供后面调用，key为科目code
            Map<String, SubMainDetailDTO> subMainDetailDTOMap = new HashMap<>();
            for (SubMainDetailDTO e: subMainDetailDTOS) {
                String preStr = "";
                //  添加前缀
                if (e.getType() == 4) {
                    preStr = "第三方-";
                } else if (e.getType() == 5) {
                    preStr = "自有收入-";
                }
                e.setSubName(preStr + e.getSubName());
                // containsKey()检测数据科目code（e.getSubCode()）是否真实存在，然后把它放入map内
                if (!subMainDetailDTOMap.containsKey(e.getSubCode())){
                    subMainDetailDTOMap.put(e.getSubCode(),e);
                }
                // 把月总人数的mainCode设为人员
                // 月总人数（包括离职和新员工）
                if (Objects.equals(e.getSubCode(), CodeConstant.monthPeople)) {
                    SubMainDetailDTO subExportData = new SubMainDetailDTO();
                    subExportData.setSubName(e.getSubName());
                    subExportData.setSubCode(e.getSubCode());
                    subExportData.setMainCode(CodeConstant.peopleCode);
                    subExportData.setMainName("人员");
                    subExportData.setSubOrder(11);
                    subExportData.setMainOrder(1);
                    subExportDataSet.add(subExportData);
                    codes.add(e.getSubCode());
                }
                // 人均发放工资
                else if (Objects.equals(e.getSubCode(), CodeConstant.pAvgWage)) {
                    SubMainDetailDTO subExportData = new SubMainDetailDTO();
                    subExportData.setSubName(e.getSubName());
                    subExportData.setSubCode(e.getSubCode());
                    subExportData.setMainCode(CodeConstant.peopleCode);
                    subExportData.setMainName("人员");
                    subExportData.setSubOrder(13);
                    subExportData.setMainOrder(1);
                    subExportDataSet.add(subExportData);
                    codes.add(e.getSubCode());
                }
                // 人均结算收入/总人数
                else if (Objects.equals(e.getSubCode(), CodeConstant.pAvgIncome)) {
                    SubMainDetailDTO subExportData = new SubMainDetailDTO();
                    subExportData.setSubName(e.getSubName());
                    subExportData.setSubCode(e.getSubCode());
                    subExportData.setMainCode(CodeConstant.peopleCode);
                    subExportData.setMainName("人员");
                    subExportData.setSubOrder(14);
                    subExportData.setMainOrder(1);
                    subExportDataSet.add(subExportData);
                    codes.add(e.getSubCode());
                }
            }
            // 将所有dataExports放入map中供后面计算第三类科目（计算科目）的金额,key为project-code==时间, value为转化为double类型的费用开支
            dataExports.forEach(e -> {
                if (e.getDetailSubjectCode() == null) System.out.println("getDetailSubjectCode is NULL");
                if (e.getCost() == null) System.out.println("getCost is NULL");
                if (e.getYearMonth() == null) System.out.println("getYearMonth is NULL");
                codeValueMap.put(projectId.toString().concat("-").concat(e.getDetailSubjectCode().concat("==").concat(e.getYearMonth())), e.getCost().doubleValue());
            });
            // 在standardSub里获取所有的第三类科目
            List<StandardSub> standardSubs = this.standardSubService.queryByType(3);
            standardSubs.addAll(SubjectUtil.otherThirdSubjects);
            standardSubs = standardSubs.stream().filter(e -> e.getSubType()< subTYpeMax).collect(Collectors.toList());
            ScriptEngine jse = new ScriptEngineManager().getEngineByName("JavaScript");      // 计算工具
            // 第三类明细计算,通过将公式(科目表里的calculate_rule)里的code替换成真实金额，在通过ScriptEngine实例计算
            for (StandardSub standardSub : standardSubs) {
                String code = standardSub.getCode();
                // subMainDetailDTOMap内有所有的科目，在所有的科目中查找类型为3的code
                SubMainDetailDTO subMainDetailDTO = subMainDetailDTOMap.get(code);
                // 获取到计算规则用rule接收
                String rule = standardSub.getCalculateRule();
                // getRuleCodeSet返回一个装有规则的集合
                Set<String> ruleCodeSet = getRuleCodeSet(rule);
                for (String month : YearMonthMapUtils.monthList) {
                    Double reuslt = 0d;
                    String yearMonth = year + "-" + month;
                    // 判断类别，为 4 ,5 类直接累加计算, 否则通过存入公式计算
                    if (subMainDetailDTO.getSubName().equals("第三方收入合计")) {
                        for (DetailCost e : dataExports) {
                            if (e.getYearMonth().equals(yearMonth) && e.getType() == 4) {
                                reuslt += Double.parseDouble(e.getCost().toString());
                            }
                        }
                    } else if (subMainDetailDTO.getSubName().equals("自有收入合计")) {
                        for (DetailCost e : dataExports) {
                            if (e.getYearMonth().equals(yearMonth) && e.getType() == 5) {
                                reuslt += Double.parseDouble(e.getCost().toString());
                            }
                        }
                    } else if (subMainDetailDTO.getSubName().equals("第三方支出合计")) {
                        String key = CodeConstant.pendingPaymentCode;
                        for (DetailCost e : dataExports) {
                            if (e.getYearMonth().equals(yearMonth) && (e.getType() == 6 || e.getDetailSubjectCode().equals(key))) {
                                reuslt += Double.parseDouble(e.getCost().toString());
                            }
                        }
                    } else if (subMainDetailDTO.getSubName().equals(CompanyConstant.companyName + "项目收入")) {
                        // 加上自有收入合计和第三方收入合计
                        String key = projectId.toString().concat("-").concat(CodeConstant.selfIncomeCode + "==" + yearMonth);
                        reuslt += codeValueMap.get(key);
                        key = projectId.toString().concat("-").concat(CodeConstant.thirdPartCode + "==" + yearMonth);
                        reuslt += codeValueMap.get(key);
                    } else if (subMainDetailDTO.getSubName().equals("人均发放工资") || subMainDetailDTO.getSubName().equals("人均结算收入/总人数")) {
                        continue;
                    }
                    else {
                        // 通过公式计算
                        String standardSubKey = projectId.toString().concat("-").concat(standardSub.getCode() + "==" + yearMonth);
                        if (codeValueMap.containsKey(standardSubKey)) {
                            continue;
                        }
                        String realRule = rule;
                        for (String ruleCode : ruleCodeSet) {
                            String key = projectId.toString().concat("-").concat(ruleCode.concat("==").concat(year).concat("-").concat(month));
                            Double value = codeValueMap.get(key);
                            if (Objects.isNull(value)) {
                                value = 0d;
                            }
                            realRule = realRule.replaceAll("【" + ruleCode + "】", value.toString());
                        }
                        //计算公式
                        try {
                            reuslt = (Double) jse.eval(realRule);
                        } catch (ScriptException e) {
                            e.printStackTrace();
                        }
                        if (reuslt.isNaN() || reuslt.isInfinite()) {
                            reuslt = 0d;
                        }
                    }
                    reuslt = Double.parseDouble(String.format("%.2f",reuslt));
                    codeValueMap.put(projectId.toString().concat("-").concat(code.concat("==").concat(yearMonth)), reuslt);
                }
            }
            subMainDetailDTOS = subMainDetailDTOS.stream().filter(e -> e.getSubType()!=-1 && e.getSubType()<subTYpeMax ).collect(Collectors.toList());
            for (SubMainDetailDTO subMainDetailDTO : subMainDetailDTOS) {
                Double result1 = 0d;
                for (String yearMonth : yearMonths) {
                    String key = projectId.toString().concat("-").concat(subMainDetailDTO.getSubCode().concat("==").concat(yearMonth));
                    result1 += codeValueMap.getOrDefault(key, 0d);
                }
                if(subMainDetailDTO.getSubType() == 1) {
                    codeValueMap1.put(projectId.toString().concat(("-").concat(subMainDetailDTO.getSubCode())), result1);
                    if(!codes.contains(subMainDetailDTO.getSubCode())) {
                        SubMainDetailDTO data = new SubMainDetailDTO();
                        data.setSubName(subMainDetailDTO.getSubName());
                        data.setSubCode(subMainDetailDTO.getSubCode());
                        data.setMainName(subMainDetailDTO.getMainName());
                        data.setMainCode(subMainDetailDTO.getMainCode());
                        data.setMainOrder(subMainDetailDTO.getMainOrder());
                        // 招聘费的order太大，需要更改
                        if(subMainDetailDTO.getSubCode().equals(CodeConstant.recruitFeeCode)) {
                            data.setSubOrder(1110);
                        }
                        else {
                            data.setSubOrder(subMainDetailDTO.getSubOrder());
                        }
                        subExportDataSet.add(data);
                        codes.add(subMainDetailDTO.getSubCode());
                    }
                }
                else if(subMainDetailDTO.getSubType() == 2) {
                    codeValueMap2.put(projectId.toString().concat(("-").concat(subMainDetailDTO.getSubCode())), result1);
                }
                else if(subMainDetailDTO.getSubType() == 3) {
                    codeValueMap3.put(projectId.toString().concat(("-").concat(subMainDetailDTO.getSubCode())), result1);
                }
                else if(subMainDetailDTO.getSubType() == 4) {
                    // 月总人数和月平均人数的值放入codeValueMap里
                    codeValueMap1.put(projectId.toString().concat(("-").concat(subMainDetailDTO.getSubCode())), result1);
                    double result2 = Double.parseDouble(String.format("%.2f",result1/yearMonths.size()));
                    codeValueMap1.put(projectId.toString().concat(("-").concat(CodeConstant.PeopleAvgCnt)), result2);
                    SubMainDetailDTO data = new SubMainDetailDTO();
                    data.setSubName("月平均人数");
                    data.setSubCode(CodeConstant.PeopleAvgCnt);
                    data.setMainName("人员");
                    data.setMainCode(CodeConstant.peopleCode);
                    data.setSubOrder(12);
                    data.setMainOrder(1);
                    subExportDataSet.add(data);
                    codes.add(data.getSubCode());
                }
                else if(subMainDetailDTO.getSubType()>0) {
                    codeValueMap1.put(projectId.toString().concat(("-").concat(subMainDetailDTO.getSubCode())), result1);
                    subExportDataSet.add(subMainDetailDTO);
                    codes.add(subMainDetailDTO.getSubCode());
                }
            }
            double result2 = 0d;
            for (double value : codeValueMap2.values()) {
                result2 += value;
            }
            double result3 = 0d;
            for (double value : codeValueMap3.values()) {
                result3 += value;
            }
            codeValueMap1.put(projectId.toString().concat(("-").concat(CodeConstant.placeCost)), result2);
            codeValueMap1.put(projectId.toString().concat(("-").concat(CodeConstant.dailyCost)), result3);
//            codeValueMap1.put(projectId.toString().concat(("-").concat(CodeConstant.subsidySum)), result4);
            SubMainDetailDTO data = new SubMainDetailDTO();
            data.setMainName("费用");
            data.setMainCode(CodeConstant.costCode);
            data.setMainOrder(20);
            data.setSubName("场地费");
            data.setSubCode(CodeConstant.placeCost);
            data.setSubOrder(310);
            subExportDataSet.add(data);
            codes.add(data.getSubCode());

            data = new SubMainDetailDTO();
            data.setMainName("费用");
            data.setMainCode(CodeConstant.costCode);
            data.setMainOrder(20);
            data.setSubName("日常费用");
            data.setSubCode(CodeConstant.dailyCost);
            data.setSubOrder(2110);
            subExportDataSet.add(data);
            codes.add(data.getSubCode());
// 计算人均发放工资和人均结算收入/总人数
            String peopleKey = projectId.toString().concat(("-").concat(CodeConstant.monthPeople));
            double result4 = 0;
            double result5 = 0;
            if(codeValueMap1.getOrDefault(peopleKey, 0D)!=0) {
                result4 = 1.0 * codeValueMap1.getOrDefault(projectId.toString().concat(("-").concat(CodeConstant.shouldPay)), 0D)/codeValueMap1.getOrDefault(peopleKey, 0D);
                result4 = Double.parseDouble(String.format("%.2f", result4));
                result5 = 1.0 * codeValueMap1.getOrDefault(projectId.toString().concat(("-").concat(CodeConstant.selfIncomeCode)), 0D)/codeValueMap1.getOrDefault(peopleKey, 0D);
                result5 = Double.parseDouble(String.format("%.2f", result5));
            }
            codeValueMap1.put(projectId.toString().concat(("-").concat(CodeConstant.pAvgWage)), result4);
            codeValueMap1.put(projectId.toString().concat(("-").concat(CodeConstant.pAvgIncome)), result5);
        }
        projects.sort(Comparator.comparing(Project::getTypeId));
        int num = 2+projects.size()+types.size()+1-1;
        XSSFSheet sheet = workbook.createSheet(year+"年汇总表");   // 创建一个表
        setColWidth(sheet, num);
        List<String> subList = getSubTitle(projects, types, year);
            //设置标题
        setMainTitle(sheet, workbook,  num, year);
        //设置子标题
        setSubTitle(sheet, workbook, projects, subList);
        // 主体内容
        setDetailData(sheet, workbook, projects, codeValueMap1, subExportDataSet, subList, year, excelType);
        // 合并单元格
//        CellRangeAddress cellRangeAddress = new CellRangeAddress(31, 31, 0, 1);
//        sheet.addMergedRegion(cellRangeAddress);
        CellRangeAddress cellRangeAddress = new CellRangeAddress(37, 37, 0, 1);
        sheet.addMergedRegion(cellRangeAddress);
        cellRangeAddress = new CellRangeAddress(38, 38, 0, 1);
        sheet.addMergedRegion(cellRangeAddress);
        cellRangeAddress = new CellRangeAddress(44, 44, 0, 1);
        sheet.addMergedRegion(cellRangeAddress);
        cellRangeAddress = new CellRangeAddress(45, 45, 0, 1);
        sheet.addMergedRegion(cellRangeAddress);
        sheet.createFreezePane(2, 3);
    }
    public void setColWidth(XSSFSheet spreadsheet, int num) {
        spreadsheet.setColumnWidth(0, 252 * 27 + 323);
        spreadsheet.setColumnWidth(1, 252 * 27 + 323);
        for (int i = 2; i <= num; i++) {
            spreadsheet.setColumnWidth(i, 252 * 17 + 323);
        }
    }

    public void setMainTitle(XSSFSheet sheet, XSSFWorkbook workbook, int num, String year) {
        XSSFRow row = sheet.createRow(0);     // 创建一行

        XSSFCellStyle titleStyle = StyleMap.getTitleStyle(workbook);
        // 合并单元格
        CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 1, 0, num);
        sheet.addMergedRegion(cellRangeAddress);

        XSSFCell cell = row.createCell(0);  // 在row行columnIndex列创建单元格
        cell.setCellValue(year + "年项目统计汇总表"); // 添加单元格内容
        cell.setCellStyle(titleStyle);
        row.setHeightInPoints(24);
    }

    public void setSubTitle(XSSFSheet sheet, XSSFWorkbook workbook, List<Project> projects, List<String> subList) {
        XSSFRow row = sheet.createRow(2);
        List<Integer> subType = new ArrayList<>();
        List<String> projectNames = new ArrayList<>();
        for (int i = 0; i < projects.size()-1; i++ ) {
            if(!Objects.equals(projects.get(i).getTypeId(), projects.get(i + 1).getTypeId())) {
                subType.add(i);
            }
            projectNames.add(projects.get(i).getProjectName());
        }
        projectNames.add(projects.get(projects.size()-1).getProjectName());
        for (int i = 0; i < subList.size(); i++) {
            XSSFCell cell = row.createCell(i);
            cell.setCellValue(subList.get(i));
            if (projectNames.contains(subList.get(i))) {
                cell.setCellStyle(StyleMap.getSecondRowStyle2(workbook));
            }
            else if(i == 0 || i == 1) {
                cell.setCellStyle(StyleMap.getSecondRowStyle1(workbook));
            }
            else if(i == subList.size() - 1) {
                cell.setCellStyle(StyleMap.getSecondRowStyle4(workbook));
            }
            else {
                cell.setCellStyle(StyleMap.getSecondRowStyle3(workbook));
            }
            row.setHeightInPoints(31);
        }
    }

    private List<String> getSubTitle(List<Project> projects, List<ProjectType> types, String year) {
        List<String> subList = new ArrayList<>(Arrays.asList("科目", "明细项目"));
        for (int i = 0; i < projects.size();i++) {
            subList.add(projects.get(i).getProjectName());
            if(i < projects.size()-1 && !Objects.equals(projects.get(i).getTypeId(), projects.get(i+1).getTypeId())) {
                String id = projects.get(i).getTypeId();
                for (ProjectType type : types) {
                    if(type.getId().toString().equals(id)) {
                        subList.add(type.getProjectType());
                    }
                }
            }
        }
        for (ProjectType type : types) {
            if(type.getId().toString().equals(projects.get(projects.size()-1).getTypeId())) {
                subList.add(type.getProjectType());
            }
        }
        subList.add(year+"年合计");
        return subList;
    }

    private Integer setDetailData(XSSFSheet sheet, XSSFWorkbook workbook, List<Project> projects, Map<String, Double> codeValueMap,
                                  Set<SubMainDetailDTO> subExportDataSet, List<String> subList, String year, Integer excelType){
        int startRow = 3;
        List<SubMainDetailDTO> subMainDataList = new ArrayList<>(subExportDataSet);
        Set<String> mainSet = new HashSet<>();
        List<MainExportData> mainExportDataList = new ArrayList<>();
        Set<Integer> markSet = new HashSet<>();  // 类型标记
        List<Double> total = new ArrayList<>();  // 总计
        for (SubMainDetailDTO subData : subMainDataList) {
            if (!mainSet.contains(subData.getMainCode())) {
                MainExportData mainExportData = new MainExportData();
                mainExportData.setMainCode(subData.getMainCode());
                mainExportData.setMainName(subData.getMainName());
                mainExportData.setMainOrder(subData.getMainOrder());
                mainExportDataList.add(mainExportData);
                mainSet.add(subData.getMainCode());
            }
        }
        mainExportDataList.sort(Comparator.comparing(MainExportData::getMainOrder));
        for (MainExportData mainExportData : mainExportDataList) {
            String mainCode = mainExportData.getMainCode();
            List<SubMainDetailDTO> collect = subMainDataList.stream().filter(e -> mainCode.equals(e.getMainCode()))
                    .sorted(Comparator.comparing(SubMainDetailDTO::getSubOrder)).collect(Collectors.toList());

            List<SubExportData> subExportDataList = new ArrayList<>();

            for (SubMainDetailDTO subMainDetailDTO : collect) {
                Map<String, Double> valueMap = new HashMap<>();       // 对应的是项目，花费
                SubExportData subExportData = new SubExportData();
                subExportData.setSubName(subMainDetailDTO.getSubName());
                subExportData.setSubCode(subMainDetailDTO.getSubCode());
                double sum = 0.0;
                for(int i = 2, j = 0; i < subList.size(); i++ ) {
                    if (j < projects.size() && Objects.equals(projects.get(j).getProjectName(), subList.get(i))) {
                        double value = codeValueMap.get(projects.get(j).getId().toString().concat(("-").concat(subMainDetailDTO.getSubCode())));
                         valueMap.put(subList.get(i), value);
                        j++;
                        sum += value;
                    } else{
                        valueMap.put(subList.get(i), 0.00);  //  temp
                        markSet.add(i);
                    }
                }
                total.add(sum);
                subExportData.setValueMap(valueMap);
                subExportDataList.add(subExportData);
            }
            mainExportData.setSubExportDataList(subExportDataList);
        }
        List<Integer> marks = new ArrayList<>(markSet);
        Collections.sort(marks);
        int nowRow = 3;
        List<String> colIndex = ExportUtil.getColIndex(subList.size()); // excel列索引
        List<String> rateSubject = new ArrayList<>(Arrays.asList("e3", "e5", "e7", "e9"));  // 比例科目，单独添加公式处理
        Map<String, String> formulaMap = SubjectUtil.formulaMap;
        XSSFDataFormat df = workbook.createDataFormat();
        for (int i = 0; i < mainExportDataList.size(); i++) {
            MainExportData mainExportData = mainExportDataList.get(i);
            String mainName = mainExportData.getMainName();
            for (int j = 0; j < mainExportData.getSubExportDataList().size(); j++) {
                SubExportData subExportData = mainExportData.getSubExportDataList().get(j);
                String subName = subExportData.getSubName();

                XSSFRow row = sheet.createRow((short) (startRow + j));
                nowRow ++;
                XSSFCell firstCell = row.createCell(0);
                firstCell.setCellValue(mainName);
                XSSFCell secondCell = row.createCell(1);
                secondCell.setCellValue(subName);              //设置内容
                firstCell.setCellStyle(StyleMap.getColumnStyle1(workbook));  // 设置单元格样式
                if(Objects.equals(mainExportDataList.get(i).getMainName(), "毛利润")) {
                    secondCell.setCellStyle(StyleMap.getProfitColumnStyle(workbook));
                } else if(Objects.equals(mainExportDataList.get(i).getMainName(), subExportData.getSubName())){
                    secondCell.setCellStyle(StyleMap.getColumnStyle1(workbook));
                } else{
                    secondCell.setCellStyle(StyleMap.getColumnStyle2(workbook)); // 设置单元格样式
                }

                Map<String, Double> valueMap = subExportData.getValueMap();

                XSSFCellStyle style3 = StyleMap.getColumnStyle3(workbook, subExportData.getSubCode());  // 具体数据样式
                XSSFCellStyle style4 = StyleMap.getColumnStyle4(workbook, subExportData.getSubCode());  // 类型合计
                XSSFCellStyle style5 = StyleMap.getColumnStyle5(workbook, subExportData.getSubCode());  // 年总计
                XSSFCellStyle profitRowStyle1 = StyleMap.getProfitRowStyle1(workbook);  // 利润第一行样式
                XSSFCellStyle profitRowStyle2 = StyleMap.getProfitRowStyle2(workbook);  // 利润第二行样式
                XSSFCellStyle profitRowStyle3 = StyleMap.getProfitRowStyle3(workbook);  // 利润第三行样式
                XSSFCellStyle profitRowStyle4 = StyleMap.getProfitRowStyle4(workbook);  // 利润第四行样式
                XSSFCellStyle profitRowStyle5 = StyleMap.getProfitRowStyle5(workbook);  // 利润第五行样式
                if (Objects.nonNull(valueMap)) {
                    int start = 2;
                    int end;
                    for (int k = 2; k < subList.size(); k++) {
                        XSSFCell cell = row.createCell(k);
                        if(!marks.contains(k)) {
                            if (rateSubject.contains(subExportData.getSubCode())) {
                                cell.setCellFormula(formulaMap.get(subExportData.getSubCode()).replaceAll("col", colIndex.get(k)));
                            } else {
                                if (Objects.equals(subExportData.getSubCode(), CodeConstant.PeopleAvgCnt)) {
                                    cell.setCellValue(valueMap.get(subList.get(k)).intValue());
                                } else {
                                    cell.setCellValue(valueMap.get(subList.get(k)));
                                }
                            }
                            cell.setCellStyle(style3);
                        } else {
                            if (rateSubject.contains(subExportData.getSubCode())) {
                                cell.setCellFormula(formulaMap.get(subExportData.getSubCode()).replaceAll("col", colIndex.get(k)));
                            } else {
                                if ( k != marks.get(marks.size()-1)) {
                                    end = k-1;
                                    String formula = "SUM(" + colIndex.get(start) + nowRow + ":" + colIndex.get(end) + nowRow + ")";
                                    cell.setCellFormula(formula);
                                    start = k+1;
                                    cell.setCellStyle(style4);
                                } else  {
                                    if (Objects.equals(subExportData.getSubCode(), CodeConstant.PeopleAvgCnt)) {
                                        cell.setCellValue(total.get(nowRow-4).intValue());
                                    } else if (Objects.equals(subExportData.getSubCode(), CodeConstant.pAvgIncome)) {
                                        cell.setCellValue(Double.parseDouble(String.format("%.2f", total.get(5)/total.get(0))));
                                    } else if (Objects.equals(subExportData.getSubCode(), CodeConstant.pAvgWage)) {
                                        cell.setCellValue(Double.parseDouble(String.format("%.2f", total.get(15)/total.get(0))));
                                    } else {
                                        cell.setCellValue(total.get(nowRow-4));
                                    }
                                    cell.setCellStyle(style5);
                                }
                            }
                        }
                        if (Objects.equals(subExportData.getSubCode(), "e11")){
                            cell.setCellStyle(profitRowStyle1);
                        } else if(Objects.equals(subExportData.getSubCode(), "e1") || Objects.equals(subExportData.getSubCode(), "e2") || Objects.equals(subExportData.getSubCode(), "e4")){
                            cell.setCellStyle(profitRowStyle2);
                        } else if(Objects.equals(subExportData.getSubCode(), "e3") || Objects.equals(subExportData.getSubCode(), "e5")){
                            if (rateSubject.contains(subExportData.getSubCode())) {
                                profitRowStyle3.setDataFormat(df.getFormat("0.00%"));
                            }
                            cell.setCellStyle(profitRowStyle3);
                        } else if(Objects.equals(subExportData.getSubCode(), "e6") || Objects.equals(subExportData.getSubCode(), "e8")){
                            cell.setCellStyle(profitRowStyle4);
                        } else if(Objects.equals(subExportData.getSubCode(), "e7") || Objects.equals(subExportData.getSubCode(), "e9")){
                            if (rateSubject.contains(subExportData.getSubCode())) {
                                profitRowStyle5.setDataFormat(df.getFormat("0.00%"));
                            }
                            cell.setCellStyle(profitRowStyle5);
                        }
                    }
                }
            }
            if(mainExportData.getSubExportDataList().size() > 1) {
                CellRangeAddress cellRangeMain = new CellRangeAddress(startRow, startRow + mainExportData.getSubExportDataList().size() - 1, 0, 0);
                sheet.addMergedRegion(cellRangeMain);
            }
            startRow += mainExportData.getSubExportDataList().size();
        }
        return startRow;
    }
}
